package com.cmy.www;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


public class AddressBookServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	
	private static final String URL = "jdbc:mysql://localhost:3306/bookfile";
	private static final String USER = "root";
	private static final String PASSWORD = "123456";
	
    public AddressBookServlet() {
        super();
      
    }

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request, response);
	}

	
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("utf-8");
		/**
		 * 得到其他操作界面传来的method值，并加以判断，执行相应的方法
		 */
		String method = request.getParameter("method");//获取操作
	//	System.out.println(method);
		if(method.equals("返回主页面")){
			request.getRequestDispatcher("mainPanel.jsp").forward(request, response);
		}else if(method.equals("添加")){
			addPanel(request, response);
			ArrayList<UserMas> list = selectAllPanel(request, response);
			request.setAttribute("list", list);
			request.getRequestDispatcher("show.jsp").forward(request, response);
		}else if(method.equals("查询")){
			selectPanel(request, response);
		}else if(method.equals("show")){
			/**
			 * 使用selectAllPanel()方法读取数据库中所有的通讯录信息
			 * 将数据库信息存储在List中
			 * list中的数据可供删除delPanel()和updatePanel()方法使用
			 */
			ArrayList<UserMas> list = selectAllPanel(request, response);
			request.setAttribute("list", list);
			request.getRequestDispatcher("show.jsp").forward(request, response);	
		}else if(method.equals("del")){
			delPanel(request, response);
			ArrayList<UserMas> list = selectAllPanel(request, response);
			request.setAttribute("list", list);
			request.getRequestDispatcher("show.jsp").forward(request, response);
		}else if(method.equals("确定")){
			updatePanel(request, response);
			ArrayList<UserMas> list = selectAllPanel(request, response);
			request.setAttribute("list", list);
			request.getRequestDispatcher("show.jsp").forward(request, response);		
		}else if(method.equals("显示完整通讯录")||method.equals("login")){
			ArrayList<UserMas> list = selectAllPanel(request, response);
			request.setAttribute("list", list);
			request.getRequestDispatcher("show.jsp").forward(request, response);
		}
		
		//System.out.println(method);
		//使用HashMap写法
	/*	Map<String, UserMas> hm = new HashMap<String, UserMas>();
		
		HttpSession se = request.getSession();//创建session对象
		if(se.getAttribute("user")==null){
			UserMas user = new UserMas();
			se.setAttribute("user", user);
		}
		UserMas user = (UserMas)se.getAttribute("user");*/
		
	}
	
	protected void addPanel(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String id = request.getParameter("id");
		String name = request.getParameter("name");
		String sex = request.getParameter("sex");
		String birth = request.getParameter("birth");
		String phoneNumber = request.getParameter("phoneNumber");
		String address = request.getParameter("address");
		
		//使用HashMap写法
		/*Map<String, UserMas> hm = new HashMap<String, UserMas>();
		
		HttpSession se = request.getSession();//创建session对象
		
		UserMas user = new UserMas();
		user.setId(id);
		user.setName(name);
		user.setSex(sex);
		user.setBirth(birth);
		user.setPhoneNumber(phoneNumber);
		user.setAddress(address);
		
		if(se.getAttribute("user")==null){
			se.setAttribute("user", user);
		}
		UserMas user1 = (UserMas)se.getAttribute("user");
		
		hm.put(id, user1);
		
		se.setAttribute("user",user);*/
		
		/**
		 * 连接数据库操作
		 */
		UserMas user = new UserMas();
		user.setId(id);
		user.setName(name);
		user.setSex(sex);
		user.setBirth(birth);
		user.setPhoneNumber(phoneNumber);
		user.setAddress(address);
		try {
			/**
			 * 加载驱动程序
			 * **/
			Class.forName("com.mysql.jdbc.Driver");
			/**
			 * 获取连接
			 */
			Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
			/**
			 * 创建sql语句
			 */
			String sql = "insert into addressbook(编号,姓名,性别,出生年月,联系方式,地址)values(?,?,?,?,?,?)";
			/**
			 * 创建对象语句-传送器
			 */
			PreparedStatement ps = conn.prepareStatement(sql);
			/**
			 * 发送->执行
			 */
			ps.setString(1, user.getId());
			ps.setString(2, user.getName());
			ps.setString(3, user.getSex());
			ps.setString(4, user.getBirth());
			ps.setString(5, user.getPhoneNumber());
			ps.setString(6, user.getAddress());
			ps.executeUpdate();
			
			//添加成功后跳转到主界面
//			request.getRequestDispatcher("show.jsp").forward(request, response);
			
			/**
			 * 关闭数据流
			 */
			ps.close();
			conn.close();
			
			System.out.println("add ok");
		} catch (Exception e) {
			
			e.printStackTrace();
		}
	}
	
	protected void selectPanel(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
	
		String selectChoose = request.getParameter("selectChoose");
		String selectValue = request.getParameter("selectValue");
		String sql = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection conn = null;
		
		try {
			/**
			 * 加载驱动程序
			 */
			Class.forName("com.mysql.jdbc.Driver");
			/**
			 * 获取连接
			 */
			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			/**
			 * 判断id来鉴定查找的通讯录信息是否正确
			 */
			if(selectChoose.equals("id")){
				/**
				 * 创建sql语句
				 */
				sql = "select 编号,姓名,性别,出生年月,联系方式,地址 from addressbook where 编号=?";
				/**
				 * 创建对象语句-传送器
				 */
				ps = conn.prepareStatement(sql);
				/**
				 * 发送->执行
				 */
				ps.setString(1, selectValue);
			}else if(selectChoose.equals("name")){
				sql = "select 编号,姓名,性别,出生年月,联系方式,地址 from addressbook where 姓名=?";
				/**
				 * 创建对象语句-传送器
				 */
				ps = conn.prepareStatement(sql);
				/**
				 * 发送->执行
				 */
				ps.setString(1, selectValue);
			}
			rs = ps.executeQuery();
			
			UserMas user = new UserMas();
			ArrayList<UserMas> list = new ArrayList<UserMas>();
			if(rs.next()){
				user.setId(rs.getString("编号"));
				user.setId(rs.getString("编号"));
				user.setName(rs.getString("姓名"));
				user.setSex(rs.getString("性别"));
				user.setBirth(rs.getString("出生年月"));
				user.setPhoneNumber(rs.getString("联系方式"));
				user.setAddress(rs.getString("地址"));
				list.add(user);
			}
			request.setAttribute("list", list);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			/**
			 * 查询成功后跳转到显示界面
			 */
			request.getRequestDispatcher("show.jsp").forward(request, response);
			
			/**
			 * 关闭流
			 */
			try {
				rs.close();
				ps.close();
				conn.close();
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
			System.out.println("select by " + selectChoose.toString() +" ok");
			
		}
	}
	
	protected ArrayList<UserMas> selectAllPanel(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<UserMas> list = null;
		/**
		 * 加载驱动程序
		 */
		try {
			Class.forName("com.mysql.jdbc.Driver");
			/**
			 * 获取连接
			 */
			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			/**
			 * 创建sql语句
			 */
			String sql = "select 编号,姓名,性别,出生年月,联系方式,地址 from addressbook";
			/**
			 * 创建对象语句-传送器
			 */
			ps = conn.prepareStatement(sql);
			/**
			 * 发送->执行
			 */
			rs = ps.executeQuery();
			
			UserMas user = null;
			list = new ArrayList<UserMas>();
			while(rs.next()){
				user = new UserMas();
				user.setId(rs.getString("编号"));
				user.setName(rs.getString("姓名"));
				user.setSex(rs.getString("性别"));
				user.setBirth(rs.getString("出生年月"));
				user.setPhoneNumber(rs.getString("联系方式"));
				user.setAddress(rs.getString("地址"));
				list.add(user);
			}
			//request.setAttribute("list", list);
			/*HttpSession session  = request.getSession();
			session.setAttribute("list", list);*/
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			//查询成功后跳转到主界面
		//	request.getRequestDispatcher("show.jsp").forward(request, response);
			
			/**
			 * 关闭流
			 */
			try {
				rs.close();
				ps.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			System.out.println("select ALL ok");
		
		}
		return list;
	}
	
	protected void delPanel(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
			String id = request.getParameter("delID");
			String sql2 = "delete from addressbook where 编号=?";
			PreparedStatement ps2 = conn.prepareStatement(sql2);
			
			ps2.setString(1, id);
			
			ps2.executeUpdate();
			
		} catch (Exception e) {
			
			e.printStackTrace();
		}
		System.out.println("delete ok");
		
		
//		request.getRequestDispatcher("show.jsp").forward(request, response);
	}
	protected void updatePanel(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
			
			String upID = request.getParameter("upID");
			String name = request.getParameter("name");
			
			String sql2 = "update addressbook set 姓名='?' where 编号=?";
			PreparedStatement ps2 = conn.prepareStatement(sql2);
			
			ps2.setString(1, name);
			ps2.setString(2, upID);
			System.out.println(name+upID);
			ps2.executeUpdate();
			
		} catch (Exception e) {
			
			e.printStackTrace();
		}
		System.out.println("update ok");
		

	}
}
